import pymysql
import os

class databaseSql:
    def __init__(self):
        # self.HOST_NAME = '124.207.24.170'
        # self.USER_NAME = 'aiops_demo'
        # self.PORT = 53306
        # self.PASSWORD = 'aiops2022'
        # self.DATABASE = 'aiops_demo'
        self.HOST_NAME = os.environ['HOST_NAME']
        self.USER_NAME = os.environ['USER_NAME']
        self.PORT = (int)(os.environ['PORT'])
        self.PASSWORD = os.environ['PASSWORD']
        self.DATABASE = os.environ['DATABASE']
    def insert(self, matrix, distmatrix):
        # 构造邻接矩阵字符串
        strmt = ''
        dimM = len(matrix)
        for i in range(len(matrix)):
            for j in range(len(matrix[i])):
                if (i == dimM - 1 and j == dimM - 1):
                    strmt = strmt + str(matrix[i][j])
                else:
                    strmt = strmt + str(matrix[i][j]) + ','
        print(strmt)
        # 构造dist的两个字符串
        strd1 = ''
        strd2 = ''
        dimD = len(distmatrix)
        for i in range(len(distmatrix)):
            for j in range(len(distmatrix[i])):
                if (i == dimD - 1 and j == dimD - 1):
                    strd1 = strd1 + str(distmatrix[i][j][0])
                else:
                    strd1 = strd1 + str(distmatrix[i][j][0]) + ','
        print(strd1)
        for i in range(len(distmatrix)):
            for j in range(len(distmatrix[i])):
                if (i == dimD - 1 and j == dimD - 1):
                    strd2 = strd2 + str(distmatrix[i][j][1])
                else:
                    strd2 = strd2 + str(distmatrix[i][j][1]) + ','
        print(strd2)
        #连接数据库
        db = pymysql.connect(host=self.HOST_NAME, user=self.USER_NAME, password=self.PASSWORD, port=self.PORT, db=self.DATABASE)
        cursor = db.cursor()
        # 存matrix邻接矩阵
        value = [dimM, strmt]
        sql = "INSERT INTO tcfg_graphmatrix(dim, contents) values(%s, %s)"
        # try:
        cursor.execute(sql, value)
        #得到这个图的ID
        graphInsertId = db.insert_id()
        #print(graphInsertId)
        db.commit()
        print('插入邻接矩阵数据成功')
        # except:
        #     db.rollback()
        #     print("插入数据失败")

        # 存dist矩阵
        value = [graphInsertId, dimM, strd1, strd2]
        sql = "INSERT INTO tcfg_distmatrix(graph_id, dim, dist_st, dist_ed) values(%s, %s, %s, %s)"
        # try:
        cursor.execute(sql, value)
        # print(insert_id)
        db.commit()
        print('插入DIST矩阵数据成功')
        # except:
        #     db.rollback()
        #     print("插入数据失败")


        cursor.close()
        db.close()
        return graphInsertId

    # def insertDist(self, matrix):#三维的矩阵
    #     db = pymysql.connect(host=self.HOST_NAME, user=self.USER_NAME, password=self.PASSWORD, port=self.PORT, db=self.DATABASE)
    #     cursor = db.cursor()
    #
    #
    #     value = [dim, strd1, strd2]
    #     sql = "INSERT INTO test(graph_id, dist_st) values(%s, %s)"
    #     # try:
    #     cursor.execute(sql, value)
    #     #得到这个图的ID
    #     insert_id = db.insert_id()
    #     #print(insert_id)
    #     db.commit()
    #     print('插入数据成功')
    #     # except:
    #     #     db.rollback()
    #     #     print("插入数据失败")
    #     cursor.close()
    #     db.close()
    #     return insert_id

    def search(self):
        db = pymysql.connect(host='localhost', user='root', password='root', port=3306, db='graphmatrix')
        cursor = db.cursor()

        sql = 'select * from readtest'

        #sql = 'select contents from test where id = "20220427"'
        cursor.execute(sql)

        # 5.查看结果
        # result = cursor.fetchone() #用于返回单条数据
        results = cursor.fetchall()  # 用于返回多条数据
        print(type(results))
        print(results)

        #关闭查询
        cursor.close()

        # 关闭数据库
        db.close()
        return results

# id = '20220427'
# #dim = 3
# matrix = [[1, 1, 1, 1, 1,1, 0, 0, 0],
#           [1, 1, 1, 1, 1, 1, 0, 0, 0],
#           [1, 1, 0, 1, 1, 1, 0, 0, 0],
#           [1, 1, 0, 0, 1, 1, 0, 0, 0],
#           [1, 1, 1, 1, 1, 1, 0, 0, 0],
#           [1, 1, 0, 0, 0, 0, 1, 0, 0],
#           [0, 0, 0, 0, 0, 0, 1, 1, 1],
#           [0, 0, 0, 0, 0, 0, 1, 1, 1],
#           [0, 0, 0, 0, 0, 0, 1, 1, 1]]
# #value = [dim, matrix]
#insert(matrix)

#search()
# contxt = search()[0][0]
# num = int(contxt[0])
# #print(type(contxt))
# print(contxt)
# print(num)